﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using ConsolidationTool.Common.Entities;
using System.Data;
using System.Web.Script.Serialization;

namespace ConsolidationTool.Common
{
    public class BusinessService
    {
        public static void ARTReportCreateExcel(SearchEntity cEntity, Dictionary<string, List<RQDataItem>> mainData, Dictionary<string, string> additionData)
        {
            string tempFileName = cEntity.ReportTemplateName + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, cEntity.ReportTitle);
            OpenXMLExcelBusiness.ARTReportGenericExcel(path, cEntity, mainData, additionData);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }



        public static bool validateUser(string groupName, string loginName)
        {
            try
            {
                bool result = false;
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                    {
                        using (SPWeb web = site.RootWeb)
                        {
                            SPGroup group = web.Groups[groupName];
                            foreach (SPUser user in group.Users)
                            {
                                if (user.LoginName == loginName)
                                {
                                    result = true;
                                    break;
                                }
                            }
                        }
                    }
                });
                return result;
            }
            catch (Exception)
            {
                return false;
            }
        }
        public static List<Group> getGroups(Dictionary<string, string> param)
        {
            List<Group> groups = new List<Group>();
            try
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                   {
                       using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                       {
                           using (SPWeb web = site.RootWeb)
                           {
                               foreach (KeyValuePair<string, string> item in param)
                               {
                                   SPList list = web.Lists[item.Key];
                                   SPListItemCollection items = list.Items;
                                   foreach (SPListItem itemL in items)
                                   {
                                       SPFieldUserValue userValue = new SPFieldUserValue(web, itemL[item.Value] + "");
                                       if (userValue.User == null) //group
                                       {
                                           SPGroup group = itemL.Web.SiteGroups.GetByID(userValue.LookupId);
                                           if (groups.Where(s => s.groupName == group.Name).Count() == 0)
                                           {
                                               string groupLevel = string.Empty;
                                               switch (item.Key)
                                               {
                                                   case Constants.List_Name_Entity:
                                                       groupLevel = Constants.Group_Level_Entity;
                                                       break;
                                                   case Constants.List_Name_SubRegion:
                                                       groupLevel = Constants.Group_Level_SubRegion;
                                                       break;
                                                   case Constants.List_Name_Region:
                                                       groupLevel = Constants.Group_Level_Region;
                                                       break;
                                               }
                                               Group g = new Group { groupName = group.Name, groupLevel = groupLevel };
                                               groups.Add(g);
                                           }
                                       }
                                   }
                               }
                           }
                       }
                   });
            }
            catch (Exception ex)
            {
            }
            return groups;
        }
        public static void CreateP3Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, string fileName)
        {
            string tempFileName = Constants._Brochure_P3_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP3Excel(strCurrency, path, col01, col02, col03, col04);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP6Excel(P6PL Label01, P6PL Graph01, P6PL Graph02, P6PL Graph03, P6PL Graph04, P6PL Graph05, P6PL Graph06, P6PL Graph07, P6PL Graph08, P6PL Graph09, P6PL Graph10, P6PL Graph11, P6PL Graph12, P6PL colTitle, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD)
        {
            string tempFileName = Constants._Brochure_P6_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP6Excel(Label01, Graph01, Graph02, Graph03, Graph04, Graph05, Graph06, Graph07, Graph08, Graph09, Graph10, Graph11, Graph12, colTitle, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP6CExcel(P6PL Label01, P6PL Graph01, P6PL Graph02, P6PL Graph03, P6PL Graph04, P6PL Graph05, P6PL Graph06, P6PL colTitle, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD)
        {
            string tempFileName = Constants._Brochure_P6C_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP6CExcel(Label01, Graph01, Graph02, Graph03, Graph04, Graph05, Graph06, colTitle, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP8Excel(P6PL Label01, P6PL Graph01, P6PL Graph02, P6PL Graph03, string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD, string date1, string date2, string date3, string date4)
        {
            string tempFileName = Constants._Brochure_P8_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP8Excel(Label01, Graph01, Graph02, Graph03, strCurrency, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD, date1, date2, date3, date4);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }

        public static void CreateP9Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, string fileName, string strDateDec)
        {
            string tempFileName = Constants._Brochure_P9_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP9Excel(strCurrency, path, col01, col02, col03, col04, col05, col06, strDateDec);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP10Excel(P6PL Graph01, P6PL Graph02, string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col00, string fileName)
        {
            string tempFileName = Constants._Brochure_P10_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP10Excel(Graph01, Graph02, strCurrency, path, col01, col02, col03, col04, col05, col06, col00);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP11Excel(string fileName, string strCurrentMonth, string strYTD, P6PL colData)
        {
            string tempFileName = Constants._Brochure_P11_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP11Excel(path, strCurrentMonth, strYTD, colData);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP14Excel(P6PL Label01, P6PL Graph01, P6PL Graph02, P6PL Graph03, string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strTons, string strYTD)
        {
            string tempFileName = Constants._Brochure_P14_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP14Excel(Label01, Graph01, Graph02, Graph03, strCurrency, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strTons, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP15Excel(string fileName, string strCurrentMonth, string strYTD, P6PL colData)
        {
            string tempFileName = Constants._Brochure_P15_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP15Excel(path, strCurrentMonth, strYTD, colData);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP16Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD)
        {
            string tempFileName = Constants._Brochure_P16_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP16Excel(strCurrency, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP22Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD)
        {
            string tempFileName = Constants._Brochure_P22_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP22Excel(strCurrency, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP23Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, string data1, string fileName, string month, string ytdmonth)
        {
            string tempFileName = Constants._Brochure_P23_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP23Excel(strCurrency, path, col01, col02, col03, col04, col05, col06, data1, month, ytdmonth);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateP24Excel(string strCurrency, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, string fileName, string strCurrentMonth, string strYTD)
        {
            string tempFileName = Constants._Brochure_P24_PRIFIX + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericP24Excel(strCurrency, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, strCurrentMonth, strYTD);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }
        public static void CreateHRExcel(P6PL col00, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, P6PL col11, P6PL col12, string fileName, P6PL colHead)
        {
            string tempFileName = Constants._ReportTemplate_FlashHR + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericHRExcel(col00, path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, colHead);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }

        public static void CreateQSExcel(P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, P6PL col11, P6PL col12, string fileName, P6PL colHead)
        {
            string tempFileName = Constants._ReportTemplate_FlashQS + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericQSExcel(path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, colHead);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }

        public static void CreateSCSExcel(P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, P6PL col11, P6PL col12, string fileName, P6PL colHead)
        {
            string tempFileName = Constants._ReportTemplate_FlashSCS + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericSCSExcel(path, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, colHead);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }

        public static void CreateSCSExcel(Dictionary<string, List<string>> rowData, P6PL col01, P6PL col02, P6PL col03, P6PL col04, P6PL col05, P6PL col06, P6PL col07, P6PL col08, P6PL col09, P6PL col10, P6PL col11, P6PL col12, string fileName, P6PL colHead)
        {

            string tempFileName = Constants._ReportTemplate_FlashSCS + ".xlsx";
            string path = OpenXMLExcelBusiness.GetExcelTemplatePath(tempFileName, fileName);
            OpenXMLExcelBusiness.GenericSCSExcel(path, rowData, col01, col02, col03, col04, col05, col06, col07, col08, col09, col10, col11, col12, colHead);
            OpenXMLExcelBusiness.UploadExcelToLibrary(path);
        }

        public static List<Region> getRegion()
        {
            List<Region> regionList = new List<Region>();
            //SPSecurity.RunWithElevatedPrivileges(delegate()
            //     {
            SPWeb web = SPContext.Current.Web;
            SPList list = web.Lists.TryGetList(Constants.List_Name_Region);
            if (list == null) return regionList;
            SPListItemCollection items = list.Items;
            foreach (SPListItem item in items)
            {
                Region r = new Region();
                r.regionId = int.Parse(item["ID"].ToString());
                r.regionName = item["Title"].ToString();
                r.Currency = item["Currency"] + "";
                r.Tonnage = item["Tonnage"] + "";
                r.Currency = r.Currency.IndexOf("#") != -1 ? r.Currency.Split('#')[1] : r.Currency;
                if (!string.IsNullOrEmpty(item["ConsolidationTool_Group"] + ""))
                {
                    SPFieldUserValue userValue = new SPFieldUserValue(web, item["ConsolidationTool_Group"] + "");
                    SPGroup group = item.Web.SiteGroups.GetByID(userValue.LookupId);
                    r.groupName = group.Name;
                    r.Group = group;
                }
                regionList.Add(r);
            }

            //});
            return regionList;
        }
        public static Region getRegion(string regionName)
        {
            Region r = new Region();
            if (regionName == "")
                return r;

            SPWeb web = SPContext.Current.Web;
            SPList list = web.Lists[Constants.List_Name_Region];
            SPQuery query = new SPQuery();
            query.Query = @"<Where>
                                         <Eq>
                                          <FieldRef Name='Title' />
                                          <Value Type='Text'>" + regionName + @"</Value>
                                         </Eq>
                                        </Where>";
            SPListItemCollection items = list.GetItems(query);
            if (items.Count > 0)
            {

                r.regionId = int.Parse(items[0]["ID"].ToString());
                r.regionName = items[0]["Title"].ToString();

                r.Currency = items[0]["Currency"] + "";
                r.Tonnage = items[0]["Tonnage"] + "";
                r.Currency = r.Currency.IndexOf("#") != -1 ? r.Currency.Split('#')[1] : r.Currency;
                if (!string.IsNullOrEmpty(items[0]["ConsolidationTool_Group"] + ""))
                {
                    SPFieldUserValue userValue = new SPFieldUserValue(web, items[0]["ConsolidationTool_Group"] + "");
                    SPGroup group = items[0].Web.SiteGroups.GetByID(userValue.LookupId);
                    r.groupName = group.Name;
                }
            }


            return r;
        }
        public static string getLookupFieldValue(SPListItem item, string fieldName)
        {
            try
            {
                return (item[fieldName] + "").Split('#')[1];

            }
            catch (Exception ex)
            {
                return "";
            }
        }
        public static List<SubRegion> getSubRegion()
        {
            List<SubRegion> subRegionList = new List<SubRegion>();
            //SPSecurity.RunWithElevatedPrivileges(delegate()
            //{
            SPWeb web = SPContext.Current.Web;
            SPList List = web.Lists.TryGetList(Constants.List_Name_SubRegion);
            if (List == null) return subRegionList;

            SPListItemCollection items = List.Items;
            foreach (SPListItem item in items)
            {
                SubRegion sr = new SubRegion();
                sr.subRegionId = int.Parse(item["ID"].ToString());
                sr.subRegionName = item["Title"].ToString();
                sr.regionName = getLookupFieldValue(item, "ConsolidationTool_Region");
                if (!string.IsNullOrEmpty(item["ConsolidationTool_Group"] + ""))
                {
                    SPFieldUserValue userValue = new SPFieldUserValue(web, item["ConsolidationTool_Group"] + "");
                    SPGroup group = item.Web.SiteGroups.GetByID(userValue.LookupId);
                    sr.groupName = group.Name;
                    sr.Group = group;
                }
                subRegionList.Add(sr);
            }

            //});
            return subRegionList;
        }
        public static SubRegion getSubRegion(string subRegionName)
        {
            SubRegion sr = new SubRegion();

            SPWeb web = SPContext.Current.Web;
            SPList List = web.Lists[Constants.List_Name_SubRegion];
            SPQuery query = new SPQuery();
            query.Query = @"<Where>
                                          <Eq>
                                           <FieldRef Name='Title'/>
                                           <Value Type='Text'>" + subRegionName + @"</Value>
                                          </Eq>
                                        </Where>";
            SPListItemCollection items = List.GetItems(query);
            if (items.Count > 0)
            {

                sr.subRegionId = int.Parse(items[0]["ID"].ToString());
                sr.subRegionName = items[0]["Title"].ToString();
                if (!string.IsNullOrEmpty(items[0]["ConsolidationTool_Group"] + ""))
                {
                    SPFieldUserValue userValue = new SPFieldUserValue(web, items[0]["ConsolidationTool_Group"] + "");
                    SPGroup group = items[0].Web.SiteGroups.GetByID(userValue.LookupId);
                    sr.groupName = group.Name;
                }

            }


            return sr;
        }

        public static List<SubRegion> getSubRegion(int regionId)
        {
            List<SubRegion> subRegionList = new List<SubRegion>();

            SPWeb web = SPContext.Current.Web;
            string queryStr = string.Format(@"<Where>
                                                            <Eq>      
                                                              <FieldRef Name='ConsolidationTool_Region' LookupId='TRUE'/>
                                                              <Value Type='Integer'>{0}</Value>
                                                            </Eq>
                                                        </Where>", regionId);
            SPQuery query = new SPQuery();
            query.Query = queryStr;
            query.RowLimit = Constants._LIST_QUERY_ROWLIMIT_MAX;
            SPList List = web.Lists[Constants.List_Name_SubRegion];
            SPListItemCollection items = List.GetItems(query);
            foreach (SPListItem item in items)
            {
                SubRegion sr = new SubRegion();
                sr.subRegionId = int.Parse(item["ID"].ToString());
                sr.subRegionName = item["Title"].ToString();
                sr.regionName = getLookupFieldValue(item, "ConsolidationTool_Region");
                subRegionList.Add(sr);
            }


            return subRegionList;
        }


        public static Entity getEntity(string CoCd)
        {
            var entity = new Entity();
            //SPSecurity.RunWithElevatedPrivileges(delegate()
            //{
            SPWeb web = SPContext.Current.Web;
            string queryStr = string.Format(@"<Where> 
                                                            <Eq>      
                                                              <FieldRef Name='ConsolidationTool_CoCd'/>
                                                              <Value Type='Text'>{0}</Value>
                                                            </Eq> 
                                                        </Where>", CoCd);
            SPQuery query = new SPQuery();
            query.Query = queryStr;
            query.RowLimit = Constants._LIST_QUERY_ROWLIMIT_MAX;
            SPList List = web.Lists[Constants.List_Name_Entity];
            SPListItemCollection items = List.GetItems(query);
            if (items.Count > 0)
            {
                EntityInstance(web, items[0], entity);
                //entity.ID = items[0].ID;
                //entity.CoCd = items[0]["ConsolidationTool_CoCd"].ToString();
                //entity.entityName = items[0]["Title"].ToString();
                //string curency = items[0]["Currency"].ToString();
                //curency = curency.IndexOf("#") != -1 ? curency.Split('#')[1] : curency;
                //entity.Tonnage = items[0]["Tonnage"] + "";
                //entity.subRegionName = getLookupFieldValue(items[0], "ConsolidationTool_SubRegion");
                //entity.RegionName = getLookupFieldValue(items[0], "ConsolidationTool_Region");

                //entity.Currency = curency;
                //entity.EntityFullName = Common.Utilities.GetItemValue(items[0], "Company Full Name");
            }

            //});
            return entity;
        }
        public static List<Entity> getEntity()
        {
            List<Entity> entityList = new List<Entity>();

            SPWeb web = SPContext.Current.Web;
            SPList List = web.Lists.TryGetList(Constants.List_Name_Entity);
            if (List == null) return entityList;
            SPListItemCollection items = List.Items;
            foreach (SPListItem item in items)
            {
                Entity entity = new Entity();
                EntityInstance(web, item, entity);
                entityList.Add(entity);
            }


            return entityList;
        }

        private static void EntityInstance(SPWeb web, SPListItem item, Entity entity)
        {
            entity.ID = item.ID;
            entity.CoCd = item["ConsolidationTool_CoCd"].ToString();
            entity.entityName = item["Title"].ToString();
            entity.Currency = item["Currency"].ToString();
            entity.Tonnage = item["Tonnage"] + "";
            //entity.subRegionName = getLookupFieldValue(item, "ConsolidationTool_SubRegion");
            //entity.RegionName = getLookupFieldValue(item, "ConsolidationTool_Region");

            entity.EntityFullName = item["ConsolidationTool_FullName"] + "";

            if (!string.IsNullOrEmpty(item["ConsolidationTool_Region"] + ""))
            {
                SPFieldUserValue userValue = new SPFieldUserValue(web, item["ConsolidationTool_Region"] + "");
                entity.Region = item.Web.SiteGroups[userValue.LookupValue];
                entity.RegionName = userValue.LookupValue;
            }

            if (!string.IsNullOrEmpty(item["ConsolidationTool_SubRegion"] + ""))
            {
                SPFieldUserValue RegionValue = new SPFieldUserValue(web, item["ConsolidationTool_Region"] + "");
                SPFieldUserValue SubRegionValue = new SPFieldUserValue(web, item["ConsolidationTool_SubRegion"] + "");
                entity.SubRegionGroup = item.Web.SiteGroups[RegionValue.LookupValue + "_" + SubRegionValue.LookupValue];

                entity.subRegionName = SubRegionValue.LookupValue;
            }

            if (!string.IsNullOrEmpty(item["ConsolidationTool_Group"] + ""))
            {
                SPFieldUser userField = (SPFieldUser)item.Fields.GetField("ConsolidationTool_Group");
                SPFieldUserValueCollection userFieldValueCollection = (SPFieldUserValueCollection)userField.GetFieldValue(item["ConsolidationTool_Group"].ToString());
                foreach (SPFieldUserValue userFieldValue in userFieldValueCollection)
                {
                    SPGroup group = item.Web.SiteGroups[userFieldValue.LookupValue];
                    entity.Groups.Add(group);
                }
            }
        }

        public static List<Entity> getEntity(int regionId, int subRegionId)
        {
            List<Entity> entityList = new List<Entity>();

            SPWeb web = SPContext.Current.Web;
            string queryStr = string.Format(@"<Where>
                                                            <And>
                                                            <Eq>      
                                                              <FieldRef Name='ConsolidationTool_Region' LookupId='TRUE'/>
                                                              <Value Type='Integer'>{0}</Value>
                                                            </Eq>
                                                              <Eq>      
                                                              <FieldRef Name='ConsolidationTool_SubRegion' LookupId='TRUE'/>
                                                              <Value Type='Integer'>{1}</Value>
                                                            </Eq>
                                                            </And>
                                                        </Where>", regionId, subRegionId);
            SPQuery query = new SPQuery();
            query.Query = queryStr;
            query.RowLimit = Constants._LIST_QUERY_ROWLIMIT_MAX;
            SPList List = web.Lists[Constants.List_Name_Entity];
            SPListItemCollection items = List.GetItems(query);
            foreach (SPListItem item in items)
            {
                Entity entity = new Entity();
                EntityInstance(web, item, entity);

                //entity.ID = item.ID;
                //entity.CoCd = item["ConsolidationTool_CoCd"].ToString();
                //entity.entityName = item["Title"].ToString();
                //entity.RegionName = getLookupFieldValue(item, "ConsolidationTool_Region");
                //entity.subRegionName = getLookupFieldValue(item, "ConsolidationTool_SubRegion");

                //string curency = item["Currency"].ToString();
                //curency = curency.IndexOf("#") != -1 ? curency.Split('#')[1] : curency;
                //entity.Currency = curency;
                //entity.Tonnage = item["Tonnage"] + "";
                entityList.Add(entity);
            }

            return entityList;
        }

        #region mark un use get entity code
        //        //un use
        //        public static Entity getEntityByCoCd(string coCd)
        //        {
        //            var entity = new Entity();

        //            using (SPSite site = new SPSite(SPContext.Current.Site.ID))
        //            {
        //                using (SPWeb web = site.RootWeb)
        //                {
        //                    string queryStr = string.Format(@"<Where> 
        //                                                            <Eq>      
        //                                                              <FieldRef Name='ConsolidationTool_CoCd' LookupId='TRUE'/>
        //                                                              <Value Type='Text'>{0}</Value>
        //                                                            </Eq> 
        //                                                        </Where>", coCd);
        //                    SPQuery query = new SPQuery();
        //                    query.Query = queryStr;
        //                    query.RowLimit = Constants._LIST_QUERY_ROWLIMIT_MAX;
        //                    SPList List = web.Lists[Constants.List_Name_Entity];
        //                    SPListItemCollection items = List.GetItems(query);
        //                    foreach (SPListItem item in items)
        //                    {
        //                        entity.ID = item.ID;
        //                        entity.CoCd = item["ConsolidationTool_CoCd"].ToString();
        //                        entity.entityName = item["Title"].ToString();
        //                        string curency = item["Currency"].ToString();
        //                        curency = curency.IndexOf("#") != -1 ? curency.Split('#')[1] : curency;
        //                        entity.Currency = curency;
        //                        entity.Tonnage = item["Tonnage"] + "";
        //                        entity.EntityFullName = Common.Utilities.GetItemValue(item, "Company Full Name");
        //                    }
        //                }
        //            }
        //            return entity;
        //        }

        //        //un use
        //        public static List<Entity> getEntity(int regionId)
        //        {
        //            List<Entity> entityList = new List<Entity>();

        //                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
        //                {
        //                    using (SPWeb web = site.RootWeb)
        //                    {
        //                        string queryStr = string.Format(@"<Where>
        //                                                            <Eq>      
        //                                                              <FieldRef Name='ConsolidationTool_Region' LookupId='TRUE'/>
        //                                                              <Value Type='Integer'>{0}</Value>
        //                                                            </Eq>
        //                                                        </Where>", regionId);
        //                        SPQuery query = new SPQuery();
        //                        query.Query = queryStr;
        //                        query.RowLimit = Constants._LIST_QUERY_ROWLIMIT_MAX;
        //                        SPList List = web.Lists[Constants.List_Name_Entity];
        //                        SPListItemCollection items = List.GetItems(query);
        //                        foreach (SPListItem item in items)
        //                        {
        //                            Entity en = new Entity();
        //                            en.ID = item.ID;
        //                            en.CoCd = item["ConsolidationTool_CoCd"].ToString();
        //                            en.entityName = item["Title"].ToString();
        //                            string curency = item["Currency"].ToString();
        //                            curency = curency.IndexOf("#") != -1 ? curency.Split('#')[1] : curency;
        //                            en.Currency = curency;
        //                            en.Tonnage = item["Tonnage"] + "";
        //                            entityList.Add(en);
        //                        }
        //                    }
        //                }

        //            return entityList;
        //        }
        #endregion

        public static string GetMthDays(string month, string year)
        {
            string MthDays = "";

            switch (month)
            {
                case "1":
                    MthDays = "31";
                    break;
                case "2":
                    int y = Convert.ToInt32(year);
                    int remainder = y % 4;
                    if (remainder == 0)
                    { MthDays = "29"; }
                    else
                    { MthDays = "28"; }
                    break;
                case "3":
                    MthDays = "31";
                    break;
                case "4":
                    MthDays = "30";
                    break;
                case "5":
                    MthDays = "31";
                    break;
                case "6":
                    MthDays = "30";
                    break;
                case "7":
                    MthDays = "31";
                    break;
                case "8":
                    MthDays = "31";
                    break;
                case "9":
                    MthDays = "30";
                    break;
                case "10":
                    MthDays = "31";
                    break;
                case "11":
                    MthDays = "30";
                    break;
                case "12":
                    MthDays = "31";
                    break;
                default:
                    break;
            }

            return MthDays;
        }

        #region code by mike

        public static decimal GetBudgetYTDValue(SPListItem item, string field, string month)
        {
            return GetBudgetYTDValue(item, int.Parse(month), new string[] { field }, "");
        }
        public static decimal GetBudgetYTDValue(SearchEntity se, SPListItem item)
        {
            if (item == null) return 0.00M;
            List<koOutputEntity> fieldsList = RefJsonData<koOutputEntity>(item["BillData"] + "");
            return GetBudgetYTDValue(se, fieldsList);
        }
        public static decimal GetBudgetYTDValue(SearchEntity se, List<koOutputEntity> fieldsList)
        {
            decimal value = 0.00M;
            //if (se.fields.Length > 1) //如果有多个字段，就不能先累加再计算，要先计算再累加  !!这里这样做P7表会出问题
            //{
            //    se.YTD = false;
            //    for (int i = 1; i <= se.Month; i++)
            //    {
            //        var newse = se.Clone() as SearchEntity;
            //        newse.Month = i;
            //        value += GetBudgetFieldValueByMonth(newse, fieldsList);
            //    }
            //}
            //else
            value = GetBudgetFieldValueByMonth(se, fieldsList);

            return value;
        }
        public static decimal GetBudgetYTDValue(SPListItem item, int month, string[] fields, string equ)
        {
            return GetBudgetYTDValue(new SearchEntity() { Month = month, YTD = true, fields = fields, equ = equ }, item);
        }

        public static List<string> GetBudgetLast12MonthFieldValues(string coCode, int year, int month, string listName, string[] fields, string equ, bool withCurrentMonth, bool compress, bool onlycm, string otherExp)
        {
            int round = withCurrentMonth ? 13 : 12;
            var values = new List<string>();
            decimal compressValue = 0.00M;

            if (otherExp == "fullyear")
            {

                var item = GetListItem(coCode, year, listName);
                GetBudgetYTDMonthValue(values, item, year, 12, fields, equ, withCurrentMonth, 1);
            }
            else
            {

                var item = GetListItem(coCode, year - 1, listName);
                int startMonth = withCurrentMonth ? month : month + 1;
                GetBudgetYTDMonthValue(values, item, year - 1, 12, fields, equ, withCurrentMonth, startMonth);

                item = GetListItem(coCode, year, listName);
                GetBudgetYTDMonthValue(values, item, year, month, fields, equ, withCurrentMonth, 1);
            }

            if (compress)
            {
                for (int i = 0; i < values.Count; i++)
                {
                    compressValue += decimal.Parse(values[i]);
                    values[i] = "";
                }
                values[values.Count - 1] = compressValue + "";
            }
            else if (onlycm)
            {

                for (int i = 0; i < values.Count - 1; i++)
                {
                    //compressValue += decimal.Parse(values[i]);
                    values[i] = "";
                }
            }



            return values;
        }

        public static void GetBudgetYTDMonthValue(List<string> list, SPListItem item, int year, int month, string[] fields, string equ, bool wcm, int start)
        {
            List<koOutputEntity> fieldsList = null;
            if (start > 12) return;

            if (item != null)
                fieldsList = RefJsonData<koOutputEntity>(item["BillData"] + "");

            decimal result = 0.00M;

            for (int i = start; i <= month; i++)
            {
                result = GetBudgetFieldValueByMonth(i, fields, equ, fieldsList);
                list.Add(result + "");
            }

            //  list[list.Length - 1] = result + "";

            return;
        }


        /*
        public static decimal GetKoTypeFieldValue(SearchEntity se, List<KOEntity> fieldsList)
        {
            var fieldInstance = new relaItem();
            decimal value = 0.00M;
            var entity = new KOEntity();
            string express = se.equ;
            if (string.IsNullOrEmpty(express))
            {
                fieldInstance = new relaItem(se.fields[0], '&');

                entity = fieldsList.FirstOrDefault(p => p.accountcode == fieldInstance.ac);
                value = GetObjectValue(entity, fieldInstance.field);
            }
            else
            {
                for (int a = 0; a < se.fields.Length; a++)
                {
                    fieldInstance = new relaItem(se.fields[a], '&');

                    if (fieldInstance.ac.IndexOf("|") != -1)
                    {
                        fieldsList = GetBudgetRow<KOEntity>(fieldInstance.ac.Split('|')[0], se.Entity, se.Year);
                        fieldInstance.ac = fieldInstance.ac.Split('|')[1];
                    }

                    entity = fieldsList.FirstOrDefault(p => p.accountcode == fieldInstance.ac);
                    string temp = GetObjectValue(entity, fieldInstance.field) + "";

                    express = equReplace(express, "{" + a + "}", temp + "");
                }

                string error = "";
                express = GetDivision(express);

                value = decimalParse(Evaluator.Eval(express, out error));
            }
            return value;

        } 
        */

        public static decimal GetKoTypeFieldYTDValue(SearchEntity CEntity)
        {
            decimal value = 0.00M;

            SPListItem item = BusinessService.GetListItem(CEntity.Entity, CEntity.Year, CEntity.Month, CEntity.List);
            if (item != null)
            {
                List<koOutputEntity> fieldsList = RefJsonData<koOutputEntity>(item["BillData"] + "");
                value += GetBudgetFieldValueByMonth(CEntity, fieldsList);
            }

            return value;
        }

        private static decimal GetBudgetFieldValueByMonth(int month, string[] fields, string equ, List<koOutputEntity> fieldsList)
        {
            return GetBudgetFieldValueByMonth(new SearchEntity() { Month = month, fields = fields, equ = equ }, fieldsList);
        }

        public static decimal GetBudgetFieldValueByMonth(SearchEntity se, List<koOutputEntity> fieldsList)
        {
            decimal value = 0.00M;
            string express = se.equ;
            if (fieldsList == null) return value;

            if (string.IsNullOrEmpty(express))
            {
                value = AnysisField(se, se.fields[0], fieldsList);
            }
            else
            {
                for (int a = 0; a < se.fields.Length; a++)
                {
                    string field = se.fields[a];

                    var temp = AnysisField(se, field, fieldsList);

                    express = equReplace(express, "{" + a + "}", temp + "");
                }

                string error = "";
                express = GetDivision(express);

                value = decimalParse(Evaluator.Eval(express, out error));
            }
            return value;
        }


        /// <summary>
        /// 添加处理YTD
        /// </summary>
        private static decimal AnysisField(SearchEntity se, string field, List<koOutputEntity> fieldsList)
        {
            decimal value = 0.00M;
            var copyFieldsInstances = fieldsList;
            string list = se.List;
            bool currentFieldIsBudget = se.Budget; //当前字段是否为budget字段
            if (field.IndexOf("|") != -1)
            {
                list = field.Split('|')[0];
                copyFieldsInstances = GetKoRow<koOutputEntity>(list, se, 0);
                field = field.Split('|')[1];
                currentFieldIsBudget = list.StartsWith("Budget_");
            }
            if (copyFieldsInstances == null) return value;
            var fi = new relaItem(field, '&');
            koOutputEntity entity = copyFieldsInstances.FirstOrDefault(p => p.accountcode == fi.ac);

            if (se.YTD)
            {
                for (int i = 1; i <= se.Month; i++)
                {
                    if (se.Budget && currentFieldIsBudget)
                        value += GetObjectValue(entity, GetEngMonth(i));
                    else
                    {
                        copyFieldsInstances = GetKoRow<koOutputEntity>(list, se, i);
                        if (copyFieldsInstances != null)
                        {
                            entity = copyFieldsInstances.FirstOrDefault(p => p.accountcode == fi.ac);
                            value += GetObjectValue(entity, fi.field);
                        }
                    }
                }
            }
            else
            {
                fi.field = string.IsNullOrEmpty(fi.field) ? GetEngMonth(se.Month) : fi.field;
                value = GetObjectValue(entity, fi.field);
            }
            return value;
        }


        private static List<T> GetKoRow<T>(string p, SearchEntity se, int i)
        {
            SPListItem item = null;
            i = i > 0 ? i : se.Month;

            if (se.Budget && p.StartsWith("Budget_"))
            {
                item = GetListItem(se.Entity, se.Year, p);
            }
            else
                item = GetListItem(se.Entity, se.Year, i, p);

            if (item == null) return null;

            return RefJsonData<T>(item["BillData"] + "");
        }

        public static decimal GetObjectValue(object obj, string field)
        {
            return decimalParse(obj.TryGetValue(field) + "");
        }

        public static decimal GetBudgetValue(SPListItem item, string field, string month)
        {
            return GetBudgetValue(item, field, int.Parse(month));
        }
        public static decimal GetBudgetValue(SPListItem item, string field, int month)
        {
            return GetBudgetValue(item, new string[] { field }, "", month);
        }

        public static decimal GetBudgetValue(SPListItem item, string[] fields, string equ, string month)
        {
            return GetBudgetValue(item, fields, equ, int.Parse(month));
        }
        public static decimal GetBudgetValue(SPListItem item, string[] fields, string equ, int month)
        {
            decimal value = 0.00M;
            if (item == null) return value;
            List<koOutputEntity> fieldsList = RefJsonData<koOutputEntity>(item["BillData"] + "");
            value = GetBudgetFieldValueByMonth(month, fields, equ, fieldsList);

            return value;
        }

        public static decimal GetBudgetValue(string listName, string coCode, int year, int month, string[] fields, string equ)
        {
            var item = GetListItem(coCode, year, listName);
            decimal value = 0.00M;
            if (item == null) return value;

            var se = new SearchEntity()
            {
                Entity = coCode,
                Year = year,
                Month = month,
                fields = fields,
                equ = equ,
                List = listName
            };
            List<koOutputEntity> fieldsList = RefJsonData<koOutputEntity>(item["BillData"] + "");
            value = GetBudgetFieldValueByMonth(se, fieldsList);

            return value;

        }

        public static List<string> GetReportTitle(SearchEntity se, int p)
        {
            var temp = new List<string>();
            var startDate = se.SelectedDateTime.AddMonths(-p + 1);
            for (int i = 1; i <= p; i++)
            {
                temp.Add(string.Format("{0}-{1}", BusinessService.GetEngMonth(startDate.Month), startDate.Year.ToString().Substring(2)));
                startDate = startDate.AddMonths(1);
            }
            return temp;
        }

        public static string GetEngMonth(int i)
        {
            switch (i)
            {
                case 1:
                    return "Jan";
                case 2:
                    return "Feb";
                case 3:
                    return "Mar";
                case 4:
                    return "Apr";
                case 5:
                    return "May";
                case 6:
                    return "Jun";
                case 7:
                    return "Jul";
                case 8:
                    return "Aug";
                case 9:
                    return "Sep";
                case 10:
                    return "Oct";
                case 11:
                    return "Nov";
                case 12:
                    return "Dec";

                default:
                    return "Dec";
            }
        }
        public static int GetMonth(string engmonth)
        {
            switch (engmonth)
            {
                case "Jan":
                    return 1;
                case "Feb":
                    return 2;
                case "Mar":
                    return 3;
                case "Apr":
                    return 4;
                case "May":
                    return 5;
                case "Jun":
                    return 6;
                case "Jul":
                    return 7;
                case "Aug":
                    return 8;
                case "Sep":
                    return 9;
                case "Oct":
                    return 10;
                case "Nov":
                    return 11;
                case "Dec":
                    return 12;

                default:
                    return 12;
            }
        }

        public static List<T> RefJsonData<T>(string p)
        {
            JavaScriptSerializer Serializer = new JavaScriptSerializer();
            return Serializer.Deserialize<List<T>>(p);
        }

        public static List<string> GetLast12MonthFieldValues(string coCode, int year, int month, string listName, string field)
        {
            return GetLast12MonthFieldValues(coCode, year, month, listName, new string[] { field }, "", false, false, false);
        }
        /// <summary>
        /// 获取12个月的数据
        /// </summary> 
        /// <param name="withCurrentMonth">是否包含当月</param>
        /// <param name="avg">平均值</param>
        /// <param name="R12">向上滚12个月</param>
        /// <returns></returns>
        public static List<string> GetLast12MonthFieldValues(string coCode, int year, int month, string listName, string[] field, string equ, bool withCurrentMonth, bool avg, bool R12)
        {
            var cDate = DateTime.Parse(string.Format("{0}-{1}-{2}", year, month, 1));
            int round = withCurrentMonth ? 12 : 11;
            var temp = new List<string>(round + 1);

            cDate = cDate.AddMonths(-round);
            for (int i = 0; i <= round; i++)
            {
                decimal value = 0.00M;
                if (avg)
                    value = GetLast12MonthFieldValue(coCode, cDate.Year, cDate.Month, listName, field, equ, false) / 12;
                else if (R12)
                    value = GetLast12MonthFieldValue(coCode, cDate.Year, cDate.Month, listName, field, equ, false);
                else
                    value = GetMonthFieldValue(coCode, cDate.Year, cDate.Month, listName, field, equ);

                temp.Add(value.ToString("n3"));
                cDate = cDate.AddMonths(1);
            }
            return temp;

        }


        public static void SetListValue(List<string> list, string value, int round)
        {
            string temp = list[round];
            if (string.IsNullOrEmpty(temp))
                list[round] = value;
        }

        public static decimal GetMonthFieldValue(string coCode, int year, int month, string listName, string[] fields, string equ)
        {
            decimal value = 0.00M;
            var item = GetListItem(coCode, year, month, listName);
            if (item == null)
                return value;

            value = ComplieCalc(fields, equ, item);
            return value;

        }


        public static string equReplace(string equ, string p, string p_2)
        {
            if (p_2.IsNullOrEmpty())
                p_2 = "0";

            p_2 = p_2.Replace(",", "");
            return equ.Replace(p, p_2);
        }

        public static string GetDecimalParse(string express)
        {
            return express.Replace("(", "(decimal)(");
        }
        public static string GetDivision(string express)
        {
            express = express.Replace("(", "(decimal)(");
            return string.Format("Math.Round({0}, 3)", express);
        }

        public static decimal GetLast12MonthFieldValue(string coCode, int year, int month, string listName, string field)
        {
            return GetLast12MonthFieldValue(coCode, year, month, listName, new string[] { field }, "", false);
        }

        public static decimal GetLast12MonthFieldValue(string coCode, int year, int month, string listName, string[] fields, string equ, bool withCurrentMonth)
        {
            return GetLast12MonthFieldValue(coCode, year, month, listName, fields, equ, withCurrentMonth, false);
        }

        /// <summary>
        /// get last 12 month value or ytd value
        /// </summary>
        /// <param name="withCurrentMonth"></param>
        /// <param name="YTD">get ytd value</param>
        /// <returns></returns>
        public static decimal GetLast12MonthFieldValue(string coCode, int year, int month, string listName, string[] fields, string equ, bool withCurrentMonth, bool YTD)
        {
            decimal value = 0.00M;
            int y = year;
            int y_1 = y - 1;
            int y_2 = y + 1;

            int m = month;
            int l_m = m + 1;

            if (withCurrentMonth)
                l_m = m;

            SPWeb web = SPContext.Current.Web;
            SPList list = web.Lists.TryGetList(listName);
            if (list == null) return value;
            SPQuery spQuery = new SPQuery();
            spQuery.Query = string.Format(@"<Where>
                                              <And>
                                                 <Geq>
                                                    <FieldRef Name='Year' />
                                                    <Value Type='Integer'>{0}</Value>
                                                 </Geq>
                                                 <And>
                                                    <Leq>
                                                       <FieldRef Name='Year' />
                                                       <Value Type='Integer'>{1}</Value>
                                                    </Leq>
                                                    <Eq>
                                                       <FieldRef Name='CoCd' />
                                                       <Value Type='Text'>{2}</Value>
                                                    </Eq>
                                                 </And>
                                              </And>
                                           </Where>", y_1, y_2, coCode);
            if (listName.StartsWith("R_"))
            {
                spQuery.Query = string.Format(@"<Where>
                                                 <Geq>
                                                    <FieldRef Name='Year' />
                                                    <Value Type='Integer'>{0}</Value>
                                                 </Geq>
                                                <Leq>
                                                    <FieldRef Name='Year' />
                                                    <Value Type='Integer'>{1}</Value>
                                                </Leq>
                                              </And>
                                           </Where>", y_1, y_2);
            }
            SPListItemCollection items = list.GetItems(spQuery);
            if (items.Count > 0)
            {
                int itemCount = items.Count;
                for (int i = 0; i < itemCount; i++)
                {
                    var item = items[i];
                    int itemYear = int.Parse(item["Year"] + "");
                    int itemMonth = int.Parse(item["Month"] + "");

                    if (YTD && (itemYear == year && itemMonth <= month))
                    {
                        value += ComplieCalc(fields, equ, item);
                    }
                    else if (!YTD && ((itemYear == y_1 && itemMonth >= l_m) ||
                        (itemYear == y && itemMonth <= m)))
                    {
                        value += ComplieCalc(fields, equ, item);
                    }
                }
            }


            return Math.Round(value, 2);
        }

        public static decimal GetYTDValue(SPListItem item, string field)
        {
            return GetYTDValue(item, new string[] { field }, "");
        }
        public static decimal GetYTDValue(SPListItem item, string[] fields, string equ)
        {
            decimal value = 0.00M;
            string express = equ;

            if (string.IsNullOrEmpty(express))
                return ComplieCalc(new string[] { "CM_" + fields[0], "YTDM1_" + fields[0] }, "({0})+({1})", item);
            else
            {
                for (int a = 0; a < fields.Length; a++)
                {
                    decimal temp = ComplieCalc(new string[] { "CM_" + fields[a], "YTDM1_" + fields[a] }, "({0})+({1})", item);
                    express = equReplace(express, "{" + a + "}", temp + "");
                }
                string error = "";
                express = GetDivision(express);
                value += decimalParse(Evaluator.Eval(express, out error));
            }
            return value;
        }

        public static string GetFieldValue(SPListItem item, string field)
        {
            if (item == null)
                return "0";
            string year, month, list = "";

            if (field.IndexOf("|") != -1)
            {
                list = field.Split('|')[0];
                field = field.Split('|')[1];
                year = item["Year"] + "";
                month = item["Month"] + "";
                if (list.StartsWith("Budget"))
                    month = "";

                item = GetListItem(item["CoCd"] + "", int.Parse(year), int.Parse(month), list);
                if (item == null) return "0";

            }
            bool isKoItem = item.Fields.TryGetFieldByStaticName("BillData") != null;
            if (isKoItem)
            {
                var koTempList = RefJsonData<koOutputEntity>(item["BillData"] + "");
                var fieldInstance = new relaItem(field, '&');

                var entity = koTempList.FirstOrDefault(p => p.accountcode == fieldInstance.ac);
                return GetObjectValue(entity, fieldInstance.field) + "";

            }

            SPField f = item.Fields.TryGetFieldByStaticName(field);
            if (f != null)
                return item[field] + "";

            f = item.Fields.TryGetFieldByStaticName(field.Replace("CM_", ""));
            if (f != null)
                return item[field.Replace("CM_", "")] + "";

            if (!field.StartsWith("CM_"))
                field = "CM_" + field;

            f = item.Fields.TryGetFieldByStaticName(field);
            if (f != null)
                return item[field] + "";
            try
            {
                return item[field] + "";
            }
            catch (Exception ex)
            {
                Utilities.Log("[Unkown Field]: " + field + " List:" + item.ParentList.Title + " - " + ex.Message);
            }

            return "0";
        }

        // public static List<SPListItem> UsageItems { get; set; }

        public static SPListItem GetListItem(string entity, int year, int month, string list)
        {
            string key = entity + year + month + list;
            SPListItem item = null;

            //var item = UsageItems.FirstOrDefault(p => p.Properties["fs_key"] + "" == key);
            //if (item != null)
            //    return item;

            if (month == 0)
                item = BusinessService.GetBudgetItem(entity, year, list);
            else
                item = BusinessService.GetItem(entity, year, month, list);

            if (item == null)
                return null;

            //item.Properties["fs_key"] = key;
            //UsageItems.Add(item);
            return item;
        }

        public static SPListItem GetListItem(string entity, int year, string list)
        {
            return GetListItem(entity, year, 0, list);
        }

        public static decimal ComplieCalc(string[] fields, string equ, SPListItem item)
        {
            decimal value = 0.00M;
            if (item == null) return value;

            string express = equ;
            if (string.IsNullOrEmpty(express))
                value += decimalParse(GetFieldValue(item, fields[0]) + "");

            else
            {
                for (int a = 0; a < fields.Length; a++)
                {
                    string temp = GetFieldValue(item, fields[a]) + "";
                    express = equReplace(express, "{" + a + "}", temp + "");
                }

                string error = "";
                express = GetDivision(express);

                value += decimalParse(Evaluator.Eval(express, out error));
            }
            return value;
        }

        public static decimal decimalParse(string p)
        {
            decimal d = 0;
            if (p.IsNullOrEmpty())
                return d;
            p = p.Replace(",", "");
            decimal.TryParse(p, out d);
            return d;
        }

        #endregion

        public static SPListItemCollection GetReportConfig(string group)
        {
            SPList list = SPContext.Current.Web.Lists["BRC"];
            SPQuery spQuery = new SPQuery();
            spQuery.Query = string.Format(@"<Where>
                                  <Contains>
                                     <FieldRef Name='Category' />
                                     <Value Type='Text'>{0}</Value>
                                  </Contains>
                               </Where>", group);
            return list.GetItems(spQuery);
        }

        public static SPListItem GetItem(string coCode, int year, int month, string listName)
        {
            return GetItem(coCode, year + "", month + "", listName);
        }
        public static SPListItem GetItem(string coCode, string year, string month, string listName)
        {
            SPListItem it = null;
            SPWeb web = SPContext.Current.Web;

            SPList list = web.Lists.TryGetList(listName);
            if (list == null) return null;
            SPQuery spQuery = new SPQuery();
            spQuery.Query = @"<Where>
                                <And>
                                <And>
                                    <Eq>
                                    <FieldRef Name='Month' />
                                    <Value Type='Integer' >" + month + @"</Value>
                                    </Eq>
                                    <Eq>
                                    <FieldRef Name='Year' />
                                    <Value Type='Integer'>" + year + @"</Value>
                                    </Eq>
                                </And>
                                <Eq>
                                    <FieldRef Name='CoCd' />
                                    <Value Type='Text'>" + coCode + @"</Value> 
                                </Eq>
                                </And>
                                </Where>";

            if (listName.StartsWith("R_") || listName.StartsWith("Common_"))
            {
                spQuery.Query = string.Format(@"   <Where>
                                                                          <And>
                                                                             <Eq>
                                                                                <FieldRef Name='Year' />
                                                                                <Value Type='Integer'>{0}</Value>
                                                                             </Eq>
                                                                             <Eq>
                                                                                <FieldRef Name='Month' />
                                                                                <Value Type='Integer'>{1}</Value>
                                                                             </Eq>
                                                                          </And>
                                                                       </Where>", year, month);
            }

            SPListItemCollection items = list.GetItems(spQuery);
            if (items.Count > 0)
                it = items[0];

            return it;
        }

        public static SPListItem GetBudgetItem(string coCode, int year, string listName)
        {
            return GetBudgetItem(coCode, year + "", listName);
        }
        public static SPListItem GetBudgetItem(string coCode, string year, string listName)
        {
            SPListItem it = null;
            SPWeb web = SPContext.Current.Web;
            SPList list = web.Lists.TryGetList(listName);
            if (list == null) return null;

            SPQuery spQuery = new SPQuery();
            spQuery.Query = @"<Where>
                                <And>
                                    <Eq>
                                    <FieldRef Name='Year' />
                                    <Value Type='Integer'>" + year + @"</Value>
                                    </Eq>
                                <Eq>
                                    <FieldRef Name='CoCd' />
                                    <Value Type='Text'>" + coCode + @"</Value> 
                                </Eq>
                                </And>
                            </Where>";
            if (listName.StartsWith("R_") || listName.StartsWith("Common_"))
            {
                spQuery.Query = string.Format(@"   <Where>
                                                        <Eq>
                                                        <FieldRef Name='Year' />
                                                        <Value Type='Integer'>{0}</Value>
                                                        </Eq>
                                                </Where>", year);
            }
            SPListItemCollection items = list.GetItems(spQuery);
            if (items.Count > 0)
                it = items[0];

            return it;
        }

        public static SPListItemCollection GetSubItems(string coCode, string year, string month, string listName, string accountCode)
        {
            SPListItemCollection items = null;
            SPWeb web = SPContext.Current.Web;
            SPList list = web.Lists.TryGetList(listName);
            if (list == null) return null;
            SPQuery spQuery = new SPQuery();
            spQuery.Query = @"<Where>
                                                      <And>
                                                        <And>
                                                        <And>
                                                          <Eq>
                                                           <FieldRef Name='Month' />
                                                           <Value Type='Integer' >" + month + @"</Value>
                                                          </Eq>
                                                          <Eq>
                                                           <FieldRef Name='Year' />
                                                           <Value Type='Integer'>" + year + @"</Value>
                                                          </Eq>
                                                        </And>
                                                        <Eq>
                                                           <FieldRef Name='CoCd' />
                                                           <Value Type='Text'>" + coCode + @"</Value> 
                                                        </Eq>
                                                      </And>
                                                        <Eq>
                                                           <FieldRef Name='LM_AccountCode' />
                                                           <Value Type='Text'>" + accountCode + @"</Value> 
                                                        </Eq>
                                                      </And>
                                                     </Where>";
            if (accountCode == "*")
            {
                spQuery.Query = string.Format(@" <Where>
                                                  <And>
                                                     <Eq>
                                                        <FieldRef Name='CoCd' />
                                                        <Value Type='Text'>{0}</Value>
                                                     </Eq>
                                                     <And>
                                                        <Eq>
                                                           <FieldRef Name='Year' />
                                                           <Value Type='Integer'>{1}</Value>
                                                        </Eq>
                                                        <Eq>
                                                           <FieldRef Name='Month' />
                                                           <Value Type='Integer'>{2}</Value>
                                                        </Eq>
                                                     </And>
                                                  </And>
                                               </Where>", coCode, year, month);
            }

            items = list.GetItems(spQuery);


            return items;
        }
        /// <summary>
        /// Gety Configuration Value By key
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public static string GetConfiguration(string key)
        {
            string rst = string.Empty;
            SPSecurity.RunWithElevatedPrivileges(delegate
                ()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Constants.List_Name_Configuration];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                             <Eq>
                                              <FieldRef Name='Title' />
                                              <Value Type='Text'>" + key + @"</Value>
                                             </Eq>
                                           </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        if (items.Count > 0)
                        {
                            if (items[0]["Value"] != null)
                                rst = items[0]["Value"].ToString();
                        }

                    }
                }
            });
            return rst;
        }
        /// <summary>
        /// get rgc
        /// </summary>
        /// <returns></returns>
        public static List<RGC> GetRGC()
        {
            List<RGC> rgc = new List<RGC>();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {

                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Common.Constants.List_Name_RGC];
                        SPQuery query = new SPQuery();
                        query.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='Title'/><FieldRef Name='Category'/><FieldRef Name='Description'/>";
                        SPListItemCollection items = list.GetItems(query);
                        for (int ii = 0; ii < items.Count; ii++)
                        {
                            rgc.Add(new RGC
                            {
                                ID = items[ii].ID.ToString(),
                                Title = items[ii].Title,
                                Category = (items[ii]["Category"] == null ? "" : items[ii]["Category"].ToString()),
                                Description = (items[ii]["Description"] == null ? "" : items[ii]["Description"].ToString())
                            });
                        }

                    }
                }
            });
            return rgc;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="rgcID"></param>
        /// <returns></returns>
        public static List<SubRGC> GetSubRGCByRGCID(string rgcID)
        {
            List<SubRGC> sugRGC = new List<SubRGC>();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {

                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Common.Constants.List_Name_SubRGC];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                    <Eq>
                                     <FieldRef Name='RGC' LookupId='True' />
                                     <Value Type='Lookup'>" + rgcID + @"</Value>
                                    </Eq>
                                  </Where>";
                        query.ViewFields = "<FieldRef Name='ID'/><FieldRef Name='Title'/><FieldRef Name='RGC'/><FieldRef Name='Description'/>";
                        SPListItemCollection items = list.GetItems(query);
                        for (int ii = 0; ii < items.Count; ii++)
                        {
                            sugRGC.Add(new SubRGC
                            {
                                ID = items[ii].ID.ToString(),
                                Title = items[ii].Title,
                                RGCID = items[ii]["RGC"].ToString(),
                                Description = items[ii]["Description"].ToString()
                            });
                        }
                    }
                }
            });
            return sugRGC;
        }

        public static string GetRGCDescriptionByRGC(string rgcID)
        {
            string Description = "";
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Common.Constants.List_Name_RGC];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                    <Eq>
                                     <FieldRef Name='RGC' LookupId='True' />
                                     <Value Type='Text'>" + rgcID + @"</Value>
                                    </Eq>
                                  </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        if (items.Count > 0)
                        {
                            SPListItem it = null;
                            it = items[0];
                            Description = it["Description"].ToString();
                        }


                    }
                }
            });
            return Description;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="?"></param>
        /// <returns></returns>
        public static List<SubRGC> GetSubRGCByID(string id)
        {
            List<SubRGC> subRgc = new List<SubRGC>();
            using (SPSite site = new SPSite(SPContext.Current.Site.ID))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList list = web.Lists[Common.Constants.List_Name_SubRGC];
                    SPListItem it = list.GetItemById(Convert.ToInt32(id));
                    subRgc.Add(new SubRGC
                    {
                        ID = it.ID.ToString(),
                        Title = it.Title,
                        Description = it["Description"].ToString()


                    });
                }
            }
            return subRgc;
        }
        public static List<RGC> GetRGCByID(string id)
        {
            List<RGC> Rgc = new List<RGC>();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {


                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Common.Constants.List_Name_RGC];
                        SPListItem it = list.GetItemById(Convert.ToInt32(id));
                        Rgc.Add(new RGC
                        {
                            ID = it.ID.ToString(),
                            Title = it.Title,
                            Description = it["Description"].ToString()


                        });
                    }
                }
            });
            return Rgc;
        }
        public static List<RGC> GetRGCByCategory(string category)
        {
            List<RGC> listRgc = GetRGC();
            //var lst = from rgc in listRgc where rgc.Category == category select new { rgc.ID, rgc.Title, rgc.Description };
            return listRgc.Where(r => r.Category == category).ToList();

        }
        /// <summary>
        /// 获取表单中的RGC
        /// </summary>
        /// <param name="listName">列表名称</param>
        /// <param name="regionId">Regionid</param>
        /// <param name="subregionId">SubRegionId</param>
        /// <param name="entityId">实体ID</param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static List<RGCInParentForm> GetRGCByParentForm(string listName, string regionId, string subregionId, string entityId, string year)
        {
            List<RGCInParentForm> rgc = new List<RGCInParentForm>();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[listName];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                          <And>
                                            <And>
                                             <And>
                                              <Eq>
                                               <FieldRef Name='Region' LookupId='True'/>
                                               <Value Type='Lookup'>" + regionId + @"</Value>
                                              </Eq>
                                              <Eq>
                                               <FieldRef Name='SubRegion' LookupId='True'/>
                                               <Value Type='Lookup'>" + subregionId + @"</Value>
                                              </Eq>
                                            </And>
                                            <Eq>
                                             <FieldRef Name='Entity' LookupId='True' />
                                             <Value Type='Lookup'>" + entityId + @"</Value>
                                            </Eq>
                                           </And>
                                           <Eq>
                                             <FieldRef Name='Year'/>
                                             <Value Type='Integer'>" + year + @"</Value>
                                           </Eq>
                                          </And>
                                        </Where>";
                        query.Joins = @"
                                        <Join Type='INNER' ListAlias='RGC'>
                                          <Eq>
                                           <FieldRef Name='RGC' RefType='Id'/>
                                           <FieldRef List='RGC' Name='Id' />
                                          </Eq>
                                        </Join>";
                        query.ProjectedFields = "<Field Name='Description' Type='Lookup' List='RGC' ShowField='Description' />";

                        SPListItemCollection items = list.GetItems(query);
                        SPFieldLookup rgcLkp = (SPFieldLookup)list.Fields["RGC"];

                        for (int ii = 0; ii < items.Count; ii++)
                        {
                            SPFieldLookupValue rgcLKPVal = (SPFieldLookupValue)rgcLkp.GetFieldValue(items[ii]["RGC"].ToString());
                            SPFieldLookupValue dspLKPVal = (SPFieldLookupValue)rgcLkp.GetFieldValue(items[ii]["Description"].ToString());

                            string dep = (dspLKPVal.LookupValue == null ? "" : dspLKPVal.LookupValue);

                            rgc.Add(new RGCInParentForm
                            {
                                Title = rgcLKPVal.LookupValue,
                                Description = dep,
                                ID = rgcLKPVal.LookupId.ToString(),
                                //Category = (items[ii]["Category"]==null?"":items[ii]["Category"].ToString())
                                Qty = (items[ii]["Qty"] == null ? "0" : items[ii]["Qty"].ToString()),
                                Turnover = (items[ii]["Turnover"] == null ? "0" : items[ii]["Turnover"].ToString())
                            });

                        }
                    }
                }
            });
            return rgc;
        }
        /// <summary>
        /// 删除表单中的RGC
        /// </summary>
        /// <param name="listName"></param>
        /// <param name="regionId"></param>
        /// <param name="subregionId"></param>
        /// <param name="entityId"></param>
        /// <param name="year"></param>
        public static void DeleteRGCInParentForm(string listName, string regionId, string subregionId, string entityId, string year)
        {
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[listName];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                          <And>
                                            <And>
                                             <And>
                                              <Eq>
                                               <FieldRef Name='Region' LookupId='True'/>
                                               <Value Type='Lookup'>" + regionId + @"</Value>
                                              </Eq>
                                              <Eq>
                                               <FieldRef Name='SubRegion' LookupId='True'/>
                                               <Value Type='Lookup'>" + subregionId + @"</Value>
                                              </Eq>
                                            </And>
                                            <Eq>
                                             <FieldRef Name='Entity' LookupId='True' />
                                             <Value Type='Lookup'>" + entityId + @"</Value>
                                            </Eq>
                                           </And>
                                           <Eq>
                                             <FieldRef Name='Year'/>
                                             <Value Type='Integer'>" + year + @"</Value>
                                           </Eq>
                                          </And>
                                        </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        int itemcount = items.Count;
                        for (int ii = 0; ii < itemcount; ii++)
                        {
                            items[0].Delete();
                        }
                    }
                }
            });
        }
        /// <summary>
        /// 得到SCS表单中的YTD M-1的数据
        /// </summary>
        /// <param name="year"></param>
        /// <param name="mongth"></param>
        /// <param name="companyCode"></param>
        /// <returns></returns>
        public static DataTable GetSCSYTDLastMonthData(string year, string mongth, string companyCode)
        {
            DataTable dt = new DataTable();
            Guid siteId = SPContext.Current.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Constants.List_Name_SalesCommissionService];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                         <And>
                                            <And>
                                             <Eq>
                                               <FieldRef Name='Year'/>
                                               <Value Type='Integer'>" + year + @"</Value>
                                             </Eq>
                                             <Lt>
                                               <FieldRef Name='Month'/>
                                               <Value Type='Integer'>" + mongth + @"</Value>  
                                        
                                             </Lt>
                                            </And>

                                            <Eq>
                                             <FieldRef Name='CoCd' />
                                             <Value Type='Text'>" + companyCode + @"</Value>
                                            </Eq>
                                           </And>
                                        </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        dt = items.GetDataTable();
                    }
                }
            });
            return dt;
        }
        /// <summary>
        /// 得到Purchase&Interco&declaration 表单中
        /// </summary>
        /// <param name="regionId"></param>
        /// <param name="subRegionId"></param>
        /// <param name="entityId"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static List<EntityInPID> GetEntityInPIDYTDLM(string region, string subRegion, string entity, string year, string listName)
        {

            List<EntityInPID> entityInPID = new List<EntityInPID>();
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(SPContext.Current.Site.ID))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[listName];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                               <And>
                                                <And>
                                                 <And>        
                                                  <Eq>
                                                   <FieldRef Name='Region' LookupId='True'/>
                                                   <Value Type='Lookup'>" + region + @"</Value>
                                                  </Eq>
                                                  <Eq>
                                                   <FieldRef Name='SubRegion' LookupId='True' />
                                                   <Value Type='Lookup'>" + subRegion + @"</Value>
                                                  </Eq>
                                                 </And>
                                                 <Eq>
                                                   <FieldRef Name='Entity' LookupId='True' />
                                                   <Value Type='Lookup'>" + entity + @"</Value>
                                                 </Eq>
                                                </And>
                                                <Eq>
                                                   <FieldRef Name='Year'/>
                                                   <Value Type='Integer'>" + year + @"</Value>
                                                </Eq>
                                               </And>
                                        </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        if (items != null)
                        {
                            SPFieldLookup regionLkUp = (SPFieldLookup)list.Fields["Region"];
                            SPFieldLookup subRegionLkUp = (SPFieldLookup)list.Fields["SubRegion"];
                            SPFieldLookup entityLkUp = (SPFieldLookup)list.Fields["Entity"];
                            foreach (SPListItem item in items)
                            {
                                SPFieldLookupValue regionLkVal = (SPFieldLookupValue)regionLkUp.GetFieldValue(item["Region"].ToString());
                                SPFieldLookupValue subRegionLkVal = (SPFieldLookupValue)regionLkUp.GetFieldValue(item["SubRegion"].ToString());
                                SPFieldLookupValue entityLkVal = (SPFieldLookupValue)regionLkUp.GetFieldValue(item["Entity"].ToString());
                                entityInPID.Add(new EntityInPID
                                {
                                    ItemID = item.ID,
                                    EntityID = regionLkVal.LookupId.ToString(),
                                    Qty_YTDLM = (item["Qty_YTDLM"] == null ? "0" : item["Qty_YTDLM"].ToString()),
                                    InvoiceCurrency_YTDLM = (item["InvoiceCurrency_YTDLM"] == null ? "0" : item["InvoiceCurrency_YTDLM"].ToString()),
                                    InvoiceAmount_YTDLM = (item["InvoiceAmount_YTDLM"] == null ? "0" : item["InvoiceAmount_YTDLM"].ToString()),
                                    BookAmount_YTDLM = (item["BookAmount_YTDLM"] == null ? "0" : item["BookAmount_YTDLM"].ToString()),

                                    Qty = (item["Qty"] == null ? "0" : item["Qty"].ToString()),
                                    InvoiceCurrency = (item["InvoiceCurrency"] == null ? "0" : item["InvoiceCurrency"].ToString()),
                                    InvoiceAmount = (item["InvoiceAmount"] == null ? "0" : item["InvoiceAmount"].ToString()),
                                    BookAmount = (item["BookAmount"] == null ? "0" : item["BookAmount"].ToString())
                                });
                            }
                        }
                    }
                }
            });
            return entityInPID;
        }
        /// <summary>
        /// 添加EntityInPID
        /// </summary>
        /// <param name="entitys"></param>
        /// <param name="listName"></param>
        public static void AddEntityInPID(List<EntityInPID> entitys, string listName, string regionId, string subRegionId, string entityId, string year, string month)
        {
            Guid siteId = SPContext.Current.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[listName];
                        //SPListItem it=list.
                        foreach (EntityInPID entity in entitys)
                        {
                            SPListItem item = null; ;
                            bool rst = CheckIfEntitiInPIDExist(entity.ItemID, listName);
                            if (rst)
                            {
                                item = list.GetItemById(entity.ItemID);

                            }
                            else
                            {
                                item = list.AddItem();

                                item["Region"] = regionId;
                                item["SubRegion"] = subRegionId;
                                item["Entity"] = entityId;
                                item["Year"] = year;
                                item["Month"] = month;
                            }
                            item["Qty"] = entity.Qty;
                            item["InvoiceCurrency"] = entity.InvoiceCurrency;
                            item["InvoiceAmount"] = entity.InvoiceAmount;
                            item["EntityData"] = entity.EntityID;
                            item["BookAmount"] = entity.BookAmount;

                            site.AllowUnsafeUpdates = true;
                            web.AllowUnsafeUpdates = true;
                            item.Update();
                        }
                    }
                }
            });
        }
        /// <summary>
        /// 判断Purchase&Interco&declaration中是否存在
        /// </summary>
        /// <param name="id"></param>
        /// <param name="listName"></param>
        /// <returns></returns>
        public static bool CheckIfEntitiInPIDExist(int id, string listName)
        {
            Guid siteId = SPContext.Current.Site.ID;
            bool rst = true;
            if (id < 1)
                rst = false;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[listName];
                        SPQuery query = new SPQuery();
                        query.Query = @"<Where>
                                        <Eq>
                                         <FieldRef Name='ID'/>
                                         <Value Type='Integer'>" + id + @"</Value>
                                        </Eq>  
                                       </Where>";
                        SPListItemCollection items = list.GetItems(query);
                        if (items == null || items.Count == 0)
                            rst = false;
                    }
                }
            });
            return rst;
        }
        #region SID表单
        /// <summary>
        /// 添加HeadProduct
        /// </summary>
        /// <param name="entities"></param>
        /// <param name="regionId"></param>
        /// <param name="subRegionId"></param>
        /// <param name="entityId"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        public static void AddProductInSID(List<EntityInSID> entities, string regionId, string subRegionId, string entityId, string year, string month)
        {
            Guid siteId = SPContext.Current.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Constants.List_Name_ProductSID];
                        //SPListItem it=list.
                        foreach (EntityInSID entity in entities)
                        {
                            SPListItem item = null;
                            if (entity.ItemID == 0)
                                item = list.AddItem();
                            else
                            {
                                try
                                {
                                    item = list.GetItemById(entity.ItemID);
                                }
                                catch
                                {
                                    item = list.AddItem();
                                }
                            }


                            item["Region"] = regionId;
                            item["SubRegion"] = subRegionId;
                            item["Entity"] = entityId;
                            item["Year"] = year;
                            item["Month"] = month;

                            item["Qty"] = entity.Qty;
                            item["InvoiceCurrency"] = entity.InvoiceCurrency;
                            item["InvoiceAmount"] = entity.InvoiceAmount;
                            item["EntityData"] = entity.EntityID;
                            item["BookAmount"] = entity.BookAmount;

                            site.AllowUnsafeUpdates = true;
                            web.AllowUnsafeUpdates = true;
                            item.Update();
                        }
                    }
                }
            });
        }
        /// <summary>
        /// 添加Services
        /// </summary>
        /// <param name="services"></param>
        /// <param name="regionId"></param>
        /// <param name="subRegionId"></param>
        /// <param name="entityId"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        public static void AddServicesInSID(List<ServicesInSID> services, string regionId, string subRegionId, string entityId, string year, string month)
        {
            Guid siteId = SPContext.Current.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Constants.List_Name_ServicesSID];
                        //SPListItem it=list.
                        foreach (ServicesInSID entity in services)
                        {
                            SPListItem item = null;
                            if (entity.ItemID == 0)
                                item = list.AddItem();
                            else
                            {
                                try
                                {
                                    item = list.GetItemById(entity.ItemID);
                                }
                                catch
                                {
                                    item = list.AddItem();
                                }
                            }

                            item["Region"] = regionId;
                            item["SubRegion"] = subRegionId;
                            item["Entity"] = entityId;
                            item["Year"] = year;
                            item["Month"] = month;
                            item["InvoiceCurrency"] = entity.InvoiceCurrency;
                            item["InvoiceAmount"] = entity.InvoiceAmount;
                            item["EntityData"] = entity.EntityID;
                            item["BookAmount"] = entity.BookAmount;
                            site.AllowUnsafeUpdates = true;
                            web.AllowUnsafeUpdates = true;
                            item.Update();
                        }
                    }
                }
            });
        }
        /// <summary>
        /// 添加Commission
        /// </summary>
        /// <param name="commissions"></param>
        /// <param name="regionId"></param>
        /// <param name="subRegionId"></param>
        /// <param name="entityId"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        public static void AddCommissionInSID(List<CommissionInSID> commissions, string regionId, string subRegionId, string entityId, string year, string month)
        {
            Guid siteId = SPContext.Current.Site.ID;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(siteId))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        SPList list = web.Lists[Constants.List_Name_ServicesSID];
                        //SPListItem it=list.
                        foreach (CommissionInSID entity in commissions)
                        {
                            SPListItem item = null;
                            if (entity.ItemID == 0)
                                item = list.AddItem();
                            else
                            {
                                try
                                {
                                    item = list.GetItemById(entity.ItemID);
                                }
                                catch
                                {
                                    item = list.AddItem();
                                }
                            }

                            item["Region"] = regionId;
                            item["SubRegion"] = subRegionId;
                            item["Entity"] = entityId;
                            item["Year"] = year;
                            item["Month"] = month;
                            item["InvoiceCurrency"] = entity.InvoiceCurrency;
                            item["InvoiceAmount"] = entity.InvoiceAmount;
                            item["EntityData"] = entity.EntityID;
                            item["BookAmount"] = entity.BookAmount;
                            site.AllowUnsafeUpdates = true;
                            web.AllowUnsafeUpdates = true;
                            item.Update();
                        }
                    }
                }
            });
        }
        #endregion



    }
}
